import XLSX from "xlsx";
import {dateUtil} from "../utils/dateUtil";
import xlsx from "xlsx";

export interface ExcelData<T = any> {
 header: string[];
 results: T[];
 meta: { sheetName: string };
}

function shapeWorkSheel(sheet: XLSX.WorkSheet, range: XLSX.Range) {
 let str = ' ',
     char = 65,
     customWorkSheet = {
      t: 's',
      v: str,
      r: '<t> </t><phoneticPr fontId="1" type="noConversion"/>',
      h: str,
      w: str,
     };
 if (!sheet || !sheet['!ref']) return [];
 let c = 0,
     r = 1;
 while (c < range.e.c + 1) {
  while (r < range.e.r + 1) {
   if (!sheet[String.fromCharCode(char) + r]) {
    sheet[String.fromCharCode(char) + r] = customWorkSheet;
   }
   r++;
  }
  r = 1;
  str += ' ';
  customWorkSheet = {
   t: 's',
   v: str,
   r: '<t> </t><phoneticPr fontId="1" type="noConversion"/>',
   h: str,
   w: str,
  };
  c++;
  char++;
 }
}

/**
 * @description: 第一行作为头部
 */
function getHeaderRow(sheet: XLSX.WorkSheet) {

 if (!sheet || !sheet['!ref']) return [];
 const headers: string[] = [];
 // A3:B7=>{s:{c:0, r:2}, e:{c:1, r:6}}
 const range: XLSX.Range = XLSX.utils.decode_range(sheet['!ref'] as string);
 shapeWorkSheel(sheet, range);
 const R = range.s.r;
 /* start in the first row */
 for (let C = range.s.c; C <= range.e.c; ++C) {
  /* walk every column in the range */
  const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
  /* find the cell in the first row */
  let hdr = 'UNKNOWN ' + C; // <-- replace with your desired default
  if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
  headers.push(hdr);
 }
 return headers;
}

/**
 * @description: 获得excel数据
 */
function getExcelData(workbook: XLSX.WorkBook) {
 const excelData: ExcelData[] = [];

 const dateFormat ='YYYY-MM-DD';
 const timeZone =8
 for (const sheetName of workbook.SheetNames) {
  const worksheet = workbook.Sheets[sheetName];
  const header: string[] = getHeaderRow(worksheet);
  //console.log(header)
  let results = XLSX.utils.sheet_to_json(worksheet, {
   raw: true,
   dateNF: dateFormat, //Not worked
  }) as object[];
  results = results.map((row: object) => {
   for (let field in row) {

    if (row[field] instanceof Date) {
     if (timeZone === 8) {
      row[field].setSeconds(row[field].getSeconds() + 43);
     }
     if (dateFormat) {
      row[field] = dateUtil(row[field] as string).format(dateFormat);
     }
    }
   }
   return row;
  });

  excelData.push({
   header,
   results,
   meta: {
    sheetName,
   },
  });
 }
 return excelData;
}

function getExcelSheetData(sheet: XLSX.Sheet) {

 const dateFormat ='YYYY-MM-DD';
 const timeZone =8

  const header: string[] = getHeaderRow(sheet);
  //console.log(header)
  let results = XLSX.utils.sheet_to_json(sheet, {
   raw: false,
   dateNF: dateFormat, //Not worked
  }) as object[];
  results = results.map((row: object) => {
   for (let field in row) {

    if (row[field] instanceof Date) {
     if (timeZone === 8) {
      row[field].setSeconds(row[field].getSeconds() + 43);
     }
     if (dateFormat) {
      row[field] = dateUtil(row[field] as string).format(dateFormat);
     }
    }
   }
   return row;
  });
 return {
  header,
  results,
  meta: {
   sheetName:'测试'
  },
 };
}

export default getExcelSheetData;

export function parseExcel(data,type) {
 const workBook = xlsx.read(data, { type });
 const excel = workBook.SheetNames.map((name) => ({
  SheetName: name,
  data: xlsx.utils.sheet_to_json(workBook.Sheets[name]) as any[]
 }));
}

/**
 * 将对象数据解析成 excel
 * @param {string} filePath 路径
 * @param {Array} list 数据
 * @param {string} sheetName 表格名字, 默认 Sheet1
 */
export function writeExcel(filePath, list, sheetName = "Sheet1") {
 // 新建工作簿
 const workBook = xlsx.utils.book_new();
 // 添加表格
 xlsx.utils.book_append_sheet(workBook, xlsx.utils.json_to_sheet(list), sheetName);
 // 输出文件
 xlsx.writeFile(workBook, filePath);
}